package com.witsoft.device.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.witsoft.device.entity.DeviceStatusTimeLineEntity;
import com.witsoft.device.model.DeviceRealTimeSpent;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

/**
 * @Title:时序数据Mapper接口
 * @Description:
 * @Author:zhansh
 * @Date:2021/10/20 17:01
 */
public interface DeviceStatusTimeLineDao extends BaseMapper<DeviceStatusTimeLineEntity>{


    /**
     * @desc 根据状态码获取前一天的设备的最近一条记录
     * feature(2021.12.20): 修复获取前一天设备状态记录异常的bug（实际获取的却是当天的）
     */
    @Select("select id,device_id as deviceId,status,start_time as startTime,end_time as endTime,event_time as eventTime \n"
            +" from device_status_timeline where device_id=#{deviceId} and status = #{status} and " +
            "to_char(start_time,'yyyy-MM-dd')=to_char(now() - interval '1' day,'yyyy-MM-dd') order by start_time desc limit 1")
    DeviceStatusTimeLineEntity getLastOneInfoByStatusYesterday(@Param("deviceId")String deviceId, @Param("status")String status);


    /*
     * 取某一天的某设备的运行时间总数(单位：秒)
     */
    @Select("select COALESCE(sum(total_spent),0) as seconds \n"
            +" from device_status_timeline where device_id=#{deviceId} and to_char(event_time,'yyyy-MM-dd')=#{date} and status = '1'")
    Long getSumRunningTimeByDayTime(@Param("deviceId") String deviceId, @Param("date") String date);


    /*
     * 取某一天的某设备的开机时间总数(单位：秒)
     */
    @Select("select COALESCE(sum(total_spent),0) as seconds \n"
            +" from device_status_timeline where device_id=#{deviceId} and to_char(event_time,'yyyy-MM-dd')=#{date} and status = '3'")
    Long getSumOpenningTimeByDayTime(@Param("deviceId") String deviceId, @Param("date") String date);


    /*
     * 根据状态码获取当天的设备的最近一条记录
     */
    @Select("select id,device_id as deviceId,status,start_time as startTime,end_time as endTime,event_time as eventTime \n"
            +" from device_status_timeline where device_id=#{deviceId} and status = #{status} and to_char(start_time,'yyyy-MM-dd')=to_char(now(),'yyyy-MM-dd') order by start_time desc limit 1")
    DeviceStatusTimeLineEntity getLastOneInfoByStatus(String deviceId, String status);


    /*
     * 获取当天的时序图设备列表
     * fixed（2021.11.09）：过滤时序图中状态为3,0的记录
     */
    @Select("select id,device_id as deviceId,status,start_time as startTime,end_time as endTime,event_time as eventTime \n"
            +"from device_status_timeline where to_char(event_time,'yyyy-MM-dd')=to_char(now(),'yyyy-MM-dd') and device_id=#{deviceId} and status not in ('3','0') order by start_time asc")
    public List<DeviceStatusTimeLineEntity> getListByDeviceId(String deviceId);

    /*
     * 取时间倒排第一条数据，及倒数第一条数据
     */
    @Select("select id,device_id as deviceId,status,start_time as startTime,end_time as endTime,event_time as eventTime \n"
            +" from device_status_timeline where device_id=#{deviceId} and to_char(event_time,'yyyy-MM-dd')=to_char(now(),'yyyy-MM-dd') order by event_time desc limit 1")
    DeviceStatusTimeLineEntity getLastOneInfoByDeviceId(String deviceId);

    /*
     * 取时间倒排第二条数据，及倒数第二条数据
     */
    @Select("select id,device_id as deviceId,status,start_time as startTime,end_time as endTime,event_time as eventTime \n"
            +" from device_status_timeline where device_id=#{deviceId} and to_char(event_time,'yyyy-MM-dd')=to_char(now(),'yyyy-MM-dd') order by event_time desc limit 1,1")
    DeviceStatusTimeLineEntity getLastTwoStatusRecordByDeviceId(String deviceId);

    /*
     * 取当日的运行时间总数(单位：秒)
     */
    @Select("select COALESCE(sum(date_part('hour',end_time-start_time)*60*60 + date_part('minute',end_time-start_time)*60 + date_part('second',end_time-start_time)),0) as seconds \n"
            +" from device_status_timeline where device_id=#{deviceId} and to_char(event_time,'yyyy-MM-dd')=to_char(now(),'yyyy-MM-dd') and status = '1'")
    int getSumRunningTimeDay(String deviceId);


    /*
     * 取当日某设备的运行时间总数(单位：秒) -v2 -查询方法优化
     */
    @Select("select COALESCE(sum(total_spent),0) as seconds \n"
            +" from device_status_timeline where device_id=#{deviceId} and to_char(event_time,'yyyy-MM-dd')=to_char(now(),'yyyy-MM-dd') and status = '1'")
    int getSumRunningTimeDayV2(String deviceId);


    /*
     * 取当日某设备开机时间总数(单位：秒) -v2 -查询方法优化
     */
    @Select("select COALESCE(sum(total_spent),0) as seconds \n"
            +" from device_status_timeline where device_id=#{deviceId} and to_char(event_time,'yyyy-MM-dd')=to_char(now(),'yyyy-MM-dd') and status = '3'")
    int getSumROpeningTimeDayV2(String deviceId);


    /*
     * 取当日的所有设备运行时间总数(单位：秒) -v2 -查询方法优化
     */
    @Select("select COALESCE(sum(total_spent),0) as seconds \n"
            +" from device_status_timeline where to_char(event_time,'yyyy-MM-dd')=to_char(now(),'yyyy-MM-dd') and status = '1'")
    Long getSumAllRunningTimeDayV2();


    /*
     * 取当日所有设备开机时间总数(单位：秒) -v2 -查询方法优化
     */
    @Select("select COALESCE(sum(total_spent),0) as seconds \n"
            +" from device_status_timeline where to_char(event_time,'yyyy-MM-dd')=to_char(now(),'yyyy-MM-dd') and status = '3'")
    Long getSumAllOpeningTimeDayV2();



    /*
     * 取上个月的运行时间总数(单位：秒)
     */
    @Deprecated
    @Select("select COALESCE(sum(date_part('hour',end_time-start_time)*60*60 + date_part('minute',end_time-start_time)*60 + date_part('second',end_time-start_time)),0) as seconds \n"
            +" from device_status_timeline where device_id=#{deviceId} and to_char(event_time,'yyyy-MM') = to_char(NOW() - interval '1' month ,'yyyy-MM') and status = '1'")
    Long getSumRunningMonth(String deviceId);



    /*
     * 取上个月的运行时间总数(单位：秒) -v2 -优化查询总的运行时间总值查询
     */
    @Select("select COALESCE(sum(total_spent),0) as seconds \n"
            +" from device_status_timeline where device_id=#{deviceId} and to_char(event_time,'yyyy-MM') = to_char(NOW() - interval '1' month ,'yyyy-MM') and status = '1'")
    Long getSumRunningMonthV2(String deviceId);

    /*
     * 取上个月的开机时间总数(单位：秒)
     */
    @Select("select COALESCE(sum(total_spent),0) FROM device_status_timeline where device_id=#{deviceId} and status = '3' and to_char(start_time,'yyyy-MM') = to_char(NOW() - interval '1' month ,'yyyy-MM')")
    Long getSumOPenningMonth(String deviceId);



    /*
     * 取设备当日的总时间(运行+待机+停机，单位：秒)
     */
    @Select("select COALESCE(sum(date_part('hour',end_time-start_time)*60*60 + date_part('minute',end_time-start_time)*60 + date_part('second',end_time-start_time)),0) as seconds \n"
            +" from device_status_timeline where device_id=#{deviceId} and to_char(event_time,'yyyy-MM-dd')=to_char(now(),'yyyy-MM-dd') and end_time is not null")
    int getSumTime(String deviceId);


    /*
     * 取设备当日的总开机时间-v2 sql查询优化
     */
    @Select("select COALESCE(sum(total_spent),0) as seconds \n"
            +" from device_status_timeline where device_id=#{deviceId} and to_char(event_time,'yyyy-MM-dd')=to_char(now(),'yyyy-MM-dd') and status = '3'")
    int getSumTimeV2(String deviceId);


    /*
     * 取设备当日的实时开机时间-v1 sql查询优化 2022.04.07 miki, --先拿到最新的一条（且end_time为null）的开机记录，然后拿到start_time,拿当前时间去减起始时间
     */
    @Select("select id,device_id as deviceId,status,start_time as startTime,end_time as endTime,event_time as eventTime \n"
            +" from device_status_timeline where device_id=#{deviceId} and status = '3' and to_char(start_time,'yyyy-MM-dd')=to_char(now(),'yyyy-MM-dd') and end_time is null order by start_time desc limit 1")
    DeviceStatusTimeLineEntity getRealOpeningTimeCurrentDayV1(String deviceId);


    /**
     * @desc 获取当设备状态为Running，且total_spent为null的设备 ---- 解决如果当前设备一直是运行或无停机状态，无法统计有效的运行时长  fixed:2021.11.10
     */
    @Select("select device_id,status,start_time, total_spent, extract(epoch FROM (now() - start_time)) AS real_time_spent from " +
            "device_status_timeline where to_char(start_time,'yyyy-MM-dd')=to_char(now(),'yyyy-MM-dd') AND status = '1' AND total_spent IS NULL")
    List<DeviceRealTimeSpent> getSumAllRunningTimeDayOfTotalSpentIsNull();

    /**
     * @desc 获取当设备状态为Turnning，且total_spent为null的设备时长
     */
    @Select("select device_id,status,start_time,total_spent, extract(epoch FROM (now() - start_time)) AS real_time_spent from " +
            "device_status_timeline where to_char(start_time,'yyyy-MM-dd')=to_char(now(),'yyyy-MM-dd') AND status = '3' AND total_spent IS NULL ")
    List<DeviceRealTimeSpent> getSumAllOpeningTimeDayOfTotalSpentIsNull();


    /**
     * @desc 校验当前设备total_spent为null时间后面存不存在不为空的,如果存在,不进行加处理
     */
    @Select("select count(*) from device_status_timeline where status = #{status} and start_time > #{date} and device_id =#{deviceId} and total_spent IS NOT NULL")
    Integer checkIsExistSpentIsNullAfter(Date date, String deviceId, String status);

}
